{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cell-0",
   "metadata": {},
   "source": [
    "# Pivot Vehicle Counts by EVA Fuel Type\n",
    "\n",
    "This notebook reads each **VehicleCountsByEVAFuelType** table (one per geographic\n",
    "level), pivots the `EVAFuelType` values into separate columns so that each row\n",
    "represents a single location, and then adds two derived columns:\n",
    "\n",
    "| Column | Description |\n",
    "|---|---|\n",
    "| **TotalVehicleCount** | Sum of all fuel-type counts for the location |\n",
    "| **BEVProportion** | `BEV / TotalVehicleCount` — share of battery-electric vehicles |\n",
    "\n",
    "### Geographic levels processed\n",
    "\n",
    "| Level | Input file | Location column | Output file |\n",
    "|---|---|---|---|\n",
    "| US States | `VehicleCountsByEVAFuelType_USStates.csv` | State | `Crosstab_USStates.csv` |\n",
    "| AL Counties | `VehicleCountsByEVAFuelType_ALCounties.csv` | County | `Crosstab_ALCounties.csv` |\n",
    "| AL ZIP Codes | `VehicleCountsByEVAFuelType_ALZipCodes.csv` | ZipCode | `Crosstab_ALZipCodes.csv` |\n",
    "\n",
    "### EVA Fuel Type categories\n",
    "\n",
    "| Code | Meaning |\n",
    "|---|---|\n",
    "| BEV | Battery Electric Vehicle |\n",
    "| GDV | Gasoline/Diesel Vehicle |\n",
    "| HEV | Hybrid Electric Vehicle |\n",
    "| PHEV | Plug-in Hybrid Electric Vehicle |\n",
    "| HFCV | Hydrogen Fuel Cell Vehicle (US States only) |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cell-1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-2",
   "metadata": {},
   "source": [
    "## Helper function\n",
    "\n",
    "`pivot_and_enrich` performs three operations on a long-format vehicle-count table:\n",
    "\n",
    "1. **Pivot** — moves `EVAFuelType` values into columns so each location occupies\n",
    "   exactly one row.  Any fuel types absent for a location are filled with `0`.\n",
    "2. **TotalVehicleCount** — sums every fuel-type column to give the total registered\n",
    "   vehicles at each location.\n",
    "3. **BEVProportion** — divides the BEV count by the total, giving the share of\n",
    "   battery-electric vehicles.  Locations with zero total vehicles receive a\n",
    "   proportion of `0.0` to avoid division-by-zero errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cell-3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def pivot_and_enrich(df: pl.DataFrame, location_col: str) -> pl.DataFrame:\n",
    "    \"\"\"\n",
    "    Pivot a long-format vehicle count table and add TotalVehicleCount and BEVProportion.\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    df : pl.DataFrame\n",
    "        Must contain columns: `location_col`, \"EVAFuelType\", \"Count\".\n",
    "    location_col : str\n",
    "        Name of the column that identifies locations (e.g. \"State\", \"County\", \"ZipCode\").\n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    pl.DataFrame\n",
    "        One row per location with fuel-type columns, TotalVehicleCount, and BEVProportion.\n",
    "    \"\"\"\n",
    "    # Pivot: location rows x fuel-type columns\n",
    "    pivoted = df.pivot(\n",
    "        on=\"EVAFuelType\",\n",
    "        index=location_col,\n",
    "        values=\"Count\",\n",
    "    ).fill_null(0)\n",
    "\n",
    "    # Identify the fuel-type columns (everything except the location column)\n",
    "    fuel_cols = [c for c in pivoted.columns if c != location_col]\n",
    "\n",
    "    # Add TotalVehicleCount and BEVProportion\n",
    "    pivoted = pivoted.with_columns(\n",
    "        pl.sum_horizontal(fuel_cols).alias(\"TotalVehicleCount\"),\n",
    "    ).with_columns(\n",
    "        (pl.col(\"BEV\") / pl.col(\"TotalVehicleCount\"))\n",
    "        .fill_nan(0.0)\n",
    "        .alias(\"BEVProportion\"),\n",
    "    )\n",
    "\n",
    "    return pivoted.sort(location_col)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-4",
   "metadata": {},
   "source": [
    "## 1. US States\n",
    "\n",
    "The US States table contains five fuel types (BEV, GDV, HEV, HFCV, PHEV) across\n",
    "all 50 states plus the District of Columbia."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cell-5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Input shape: (255, 3)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>State</th><th>EVAFuelType</th><th>Count</th></tr><tr><td>str</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;Alabama&quot;</td><td>&quot;BEV&quot;</td><td>17500</td></tr><tr><td>&quot;Alabama&quot;</td><td>&quot;GDV&quot;</td><td>4779800</td></tr><tr><td>&quot;Alabama&quot;</td><td>&quot;HEV&quot;</td><td>77600</td></tr><tr><td>&quot;Alabama&quot;</td><td>&quot;HFCV&quot;</td><td>0</td></tr><tr><td>&quot;Alabama&quot;</td><td>&quot;PHEV&quot;</td><td>6800</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌─────────┬─────────────┬─────────┐\n",
       "│ State   ┆ EVAFuelType ┆ Count   │\n",
       "│ ---     ┆ ---         ┆ ---     │\n",
       "│ str     ┆ str         ┆ i64     │\n",
       "╞═════════╪═════════════╪═════════╡\n",
       "│ Alabama ┆ BEV         ┆ 17500   │\n",
       "│ Alabama ┆ GDV         ┆ 4779800 │\n",
       "│ Alabama ┆ HEV         ┆ 77600   │\n",
       "│ Alabama ┆ HFCV        ┆ 0       │\n",
       "│ Alabama ┆ PHEV        ┆ 6800    │\n",
       "└─────────┴─────────────┴─────────┘"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states_long = pl.read_csv(\"../Input/VehicleCountsByEVAFuelType_USStates.csv\")\n",
    "print(\"Input shape:\", states_long.shape)\n",
    "states_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cell-6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Output shape: (51, 8)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (10, 8)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>State</th><th>BEV</th><th>GDV</th><th>HEV</th><th>HFCV</th><th>PHEV</th><th>TotalVehicleCount</th><th>BEVProportion</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;Alabama&quot;</td><td>17500</td><td>4779800</td><td>77600</td><td>0</td><td>6800</td><td>4881700</td><td>0.003585</td></tr><tr><td>&quot;Alaska&quot;</td><td>3400</td><td>553500</td><td>12100</td><td>0</td><td>1100</td><td>570100</td><td>0.005964</td></tr><tr><td>&quot;Arizona&quot;</td><td>111200</td><td>6244200</td><td>202200</td><td>0</td><td>29300</td><td>6586900</td><td>0.016882</td></tr><tr><td>&quot;Arkansas&quot;</td><td>9500</td><td>2679400</td><td>44500</td><td>0</td><td>3800</td><td>2737200</td><td>0.003471</td></tr><tr><td>&quot;California&quot;</td><td>1533900</td><td>33535700</td><td>1895100</td><td>16900</td><td>447100</td><td>37428700</td><td>0.040982</td></tr><tr><td>&quot;Colorado&quot;</td><td>127000</td><td>5147100</td><td>171200</td><td>0</td><td>48700</td><td>5494000</td><td>0.023116</td></tr><tr><td>&quot;Connecticut&quot;</td><td>39400</td><td>2775600</td><td>85200</td><td>0</td><td>22500</td><td>2922700</td><td>0.013481</td></tr><tr><td>&quot;Delaware&quot;</td><td>11100</td><td>877900</td><td>26400</td><td>0</td><td>4600</td><td>920000</td><td>0.012065</td></tr><tr><td>&quot;District of Columbia&quot;</td><td>10100</td><td>274400</td><td>19300</td><td>0</td><td>4300</td><td>308100</td><td>0.032782</td></tr><tr><td>&quot;Florida&quot;</td><td>334800</td><td>17838000</td><td>499300</td><td>0</td><td>70400</td><td>18742500</td><td>0.017863</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (10, 8)\n",
       "┌──────────────────────┬─────────┬──────────┬─────────┬───────┬────────┬───────────────────┬───────────────┐\n",
       "│ State                ┆ BEV     ┆ GDV      ┆ HEV     ┆ HFCV  ┆ PHEV   ┆ TotalVehicleCount ┆ BEVProportion │\n",
       "│ ---                  ┆ ---     ┆ ---      ┆ ---     ┆ ---   ┆ ---    ┆ ---               ┆ ---           │\n",
       "│ str                  ┆ i64     ┆ i64      ┆ i64     ┆ i64   ┆ i64    ┆ i64               ┆ f64           │\n",
       "╞══════════════════════╪═════════╪══════════╪═════════╪═══════╪════════╪═══════════════════╪═══════════════╡\n",
       "│ Alabama              ┆ 17500   ┆ 4779800  ┆ 77600   ┆ 0     ┆ 6800   ┆ 4881700           ┆ 0.003585      │\n",
       "│ Alaska               ┆ 3400    ┆ 553500   ┆ 12100   ┆ 0     ┆ 1100   ┆ 570100            ┆ 0.005964      │\n",
       "│ Arizona              ┆ 111200  ┆ 6244200  ┆ 202200  ┆ 0     ┆ 29300  ┆ 6586900           ┆ 0.016882      │\n",
       "│ Arkansas             ┆ 9500    ┆ 2679400  ┆ 44500   ┆ 0     ┆ 3800   ┆ 2737200           ┆ 0.003471      │\n",
       "│ California           ┆ 1533900 ┆ 33535700 ┆ 1895100 ┆ 16900 ┆ 447100 ┆ 37428700          ┆ 0.040982      │\n",
       "│ Colorado             ┆ 127000  ┆ 5147100  ┆ 171200  ┆ 0     ┆ 48700  ┆ 5494000           ┆ 0.023116      │\n",
       "│ Connecticut          ┆ 39400   ┆ 2775600  ┆ 85200   ┆ 0     ┆ 22500  ┆ 2922700           ┆ 0.013481      │\n",
       "│ Delaware             ┆ 11100   ┆ 877900   ┆ 26400   ┆ 0     ┆ 4600   ┆ 920000            ┆ 0.012065      │\n",
       "│ District of Columbia ┆ 10100   ┆ 274400   ┆ 19300   ┆ 0     ┆ 4300   ┆ 308100            ┆ 0.032782      │\n",
       "│ Florida              ┆ 334800  ┆ 17838000 ┆ 499300  ┆ 0     ┆ 70400  ┆ 18742500          ┆ 0.017863      │\n",
       "└──────────────────────┴─────────┴──────────┴─────────┴───────┴────────┴───────────────────┴───────────────┘"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states_wide = pivot_and_enrich(states_long, \"State\")\n",
    "print(\"Output shape:\", states_wide.shape)\n",
    "states_wide.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cell-7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved Crosstab_USStates.csv\n"
     ]
    }
   ],
   "source": [
    "states_wide.write_csv(\"Crosstab_USStates.csv\")\n",
    "print(\"Saved Crosstab_USStates.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-8",
   "metadata": {},
   "source": [
    "## 2. Alabama Counties\n",
    "\n",
    "The Alabama Counties table contains four fuel types (BEV, GDV, HEV, PHEV) for\n",
    "each of the 67 Alabama counties.  HFCV is not present at this level."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cell-9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Input shape: (268, 3)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>County</th><th>EVAFuelType</th><th>Count</th></tr><tr><td>str</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;Baldwin&quot;</td><td>&quot;BEV&quot;</td><td>1716</td></tr><tr><td>&quot;Bibb&quot;</td><td>&quot;BEV&quot;</td><td>23</td></tr><tr><td>&quot;Bullock&quot;</td><td>&quot;BEV&quot;</td><td>8</td></tr><tr><td>&quot;Butler&quot;</td><td>&quot;BEV&quot;</td><td>25</td></tr><tr><td>&quot;Chambers&quot;</td><td>&quot;BEV&quot;</td><td>54</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌──────────┬─────────────┬───────┐\n",
       "│ County   ┆ EVAFuelType ┆ Count │\n",
       "│ ---      ┆ ---         ┆ ---   │\n",
       "│ str      ┆ str         ┆ i64   │\n",
       "╞══════════╪═════════════╪═══════╡\n",
       "│ Baldwin  ┆ BEV         ┆ 1716  │\n",
       "│ Bibb     ┆ BEV         ┆ 23    │\n",
       "│ Bullock  ┆ BEV         ┆ 8     │\n",
       "│ Butler   ┆ BEV         ┆ 25    │\n",
       "│ Chambers ┆ BEV         ┆ 54    │\n",
       "└──────────┴─────────────┴───────┘"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties_long = pl.read_csv(\"../Input/VehicleCountsByEVAFuelType_ALCounties.csv\")\n",
    "print(\"Input shape:\", counties_long.shape)\n",
    "counties_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cell-10",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Output shape: (67, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (10, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>County</th><th>BEV</th><th>GDV</th><th>HEV</th><th>PHEV</th><th>TotalVehicleCount</th><th>BEVProportion</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;Autauga&quot;</td><td>307</td><td>58549</td><td>1608</td><td>104</td><td>60568</td><td>0.005069</td></tr><tr><td>&quot;Baldwin&quot;</td><td>1716</td><td>264256</td><td>9536</td><td>667</td><td>276175</td><td>0.006213</td></tr><tr><td>&quot;Barbour&quot;</td><td>31</td><td>24020</td><td>378</td><td>10</td><td>24439</td><td>0.001268</td></tr><tr><td>&quot;Bibb&quot;</td><td>23</td><td>23880</td><td>378</td><td>14</td><td>24295</td><td>0.000947</td></tr><tr><td>&quot;Blount&quot;</td><td>111</td><td>62822</td><td>1087</td><td>38</td><td>64058</td><td>0.001733</td></tr><tr><td>&quot;Bullock&quot;</td><td>8</td><td>9408</td><td>106</td><td>1</td><td>9523</td><td>0.00084</td></tr><tr><td>&quot;Butler&quot;</td><td>25</td><td>19125</td><td>270</td><td>6</td><td>19426</td><td>0.001287</td></tr><tr><td>&quot;Calhoun&quot;</td><td>332</td><td>109743</td><td>2372</td><td>83</td><td>112530</td><td>0.00295</td></tr><tr><td>&quot;Chambers&quot;</td><td>54</td><td>38490</td><td>475</td><td>12</td><td>39031</td><td>0.001384</td></tr><tr><td>&quot;Cherokee&quot;</td><td>60</td><td>31049</td><td>583</td><td>22</td><td>31714</td><td>0.001892</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (10, 7)\n",
       "┌──────────┬──────┬────────┬──────┬──────┬───────────────────┬───────────────┐\n",
       "│ County   ┆ BEV  ┆ GDV    ┆ HEV  ┆ PHEV ┆ TotalVehicleCount ┆ BEVProportion │\n",
       "│ ---      ┆ ---  ┆ ---    ┆ ---  ┆ ---  ┆ ---               ┆ ---           │\n",
       "│ str      ┆ i64  ┆ i64    ┆ i64  ┆ i64  ┆ i64               ┆ f64           │\n",
       "╞══════════╪══════╪════════╪══════╪══════╪═══════════════════╪═══════════════╡\n",
       "│ Autauga  ┆ 307  ┆ 58549  ┆ 1608 ┆ 104  ┆ 60568             ┆ 0.005069      │\n",
       "│ Baldwin  ┆ 1716 ┆ 264256 ┆ 9536 ┆ 667  ┆ 276175            ┆ 0.006213      │\n",
       "│ Barbour  ┆ 31   ┆ 24020  ┆ 378  ┆ 10   ┆ 24439             ┆ 0.001268      │\n",
       "│ Bibb     ┆ 23   ┆ 23880  ┆ 378  ┆ 14   ┆ 24295             ┆ 0.000947      │\n",
       "│ Blount   ┆ 111  ┆ 62822  ┆ 1087 ┆ 38   ┆ 64058             ┆ 0.001733      │\n",
       "│ Bullock  ┆ 8    ┆ 9408   ┆ 106  ┆ 1    ┆ 9523              ┆ 0.00084       │\n",
       "│ Butler   ┆ 25   ┆ 19125  ┆ 270  ┆ 6    ┆ 19426             ┆ 0.001287      │\n",
       "│ Calhoun  ┆ 332  ┆ 109743 ┆ 2372 ┆ 83   ┆ 112530            ┆ 0.00295       │\n",
       "│ Chambers ┆ 54   ┆ 38490  ┆ 475  ┆ 12   ┆ 39031             ┆ 0.001384      │\n",
       "│ Cherokee ┆ 60   ┆ 31049  ┆ 583  ┆ 22   ┆ 31714             ┆ 0.001892      │\n",
       "└──────────┴──────┴────────┴──────┴──────┴───────────────────┴───────────────┘"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties_wide = pivot_and_enrich(counties_long, \"County\")\n",
    "print(\"Output shape:\", counties_wide.shape)\n",
    "counties_wide.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "cell-11",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved Crosstab_ALCounties.csv\n"
     ]
    }
   ],
   "source": [
    "counties_wide.write_csv(\"Crosstab_ALCounties.csv\")\n",
    "print(\"Saved Crosstab_ALCounties.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-12",
   "metadata": {},
   "source": [
    "## 3. Alabama ZIP Codes\n",
    "\n",
    "The Alabama ZIP Codes table contains four fuel types (BEV, GDV, HEV, PHEV) for\n",
    "each ZIP code with registered vehicles in Alabama."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "cell-13",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Input shape: (2373, 3)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ZipCode</th><th>EVAFuelType</th><th>Count</th></tr><tr><td>i64</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>35004</td><td>&quot;BEV&quot;</td><td>55</td></tr><tr><td>35006</td><td>&quot;BEV&quot;</td><td>4</td></tr><tr><td>35010</td><td>&quot;BEV&quot;</td><td>44</td></tr><tr><td>35016</td><td>&quot;BEV&quot;</td><td>73</td></tr><tr><td>35020</td><td>&quot;BEV&quot;</td><td>22</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌─────────┬─────────────┬───────┐\n",
       "│ ZipCode ┆ EVAFuelType ┆ Count │\n",
       "│ ---     ┆ ---         ┆ ---   │\n",
       "│ i64     ┆ str         ┆ i64   │\n",
       "╞═════════╪═════════════╪═══════╡\n",
       "│ 35004   ┆ BEV         ┆ 55    │\n",
       "│ 35006   ┆ BEV         ┆ 4     │\n",
       "│ 35010   ┆ BEV         ┆ 44    │\n",
       "│ 35016   ┆ BEV         ┆ 73    │\n",
       "│ 35020   ┆ BEV         ┆ 22    │\n",
       "└─────────┴─────────────┴───────┘"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zipcodes_long = pl.read_csv(\"../Input/VehicleCountsByEVAFuelType_ALZipCodes.csv\")\n",
    "print(\"Input shape:\", zipcodes_long.shape)\n",
    "zipcodes_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cell-14",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Output shape: (757, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (10, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ZipCode</th><th>BEV</th><th>GDV</th><th>HEV</th><th>PHEV</th><th>TotalVehicleCount</th><th>BEVProportion</th></tr><tr><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>35004</td><td>55</td><td>11075</td><td>332</td><td>18</td><td>11480</td><td>0.004791</td></tr><tr><td>35005</td><td>14</td><td>7359</td><td>124</td><td>3</td><td>7500</td><td>0.001867</td></tr><tr><td>35006</td><td>4</td><td>3292</td><td>44</td><td>2</td><td>3342</td><td>0.001197</td></tr><tr><td>35007</td><td>134</td><td>26722</td><td>745</td><td>35</td><td>27636</td><td>0.004849</td></tr><tr><td>35010</td><td>44</td><td>19750</td><td>440</td><td>21</td><td>20255</td><td>0.002172</td></tr><tr><td>35011</td><td>0</td><td>80</td><td>0</td><td>0</td><td>80</td><td>0.0</td></tr><tr><td>35013</td><td>0</td><td>3</td><td>0</td><td>0</td><td>3</td><td>0.0</td></tr><tr><td>35014</td><td>8</td><td>4789</td><td>89</td><td>4</td><td>4890</td><td>0.001636</td></tr><tr><td>35016</td><td>73</td><td>18534</td><td>430</td><td>25</td><td>19062</td><td>0.00383</td></tr><tr><td>35019</td><td>3</td><td>2728</td><td>43</td><td>5</td><td>2779</td><td>0.00108</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (10, 7)\n",
       "┌─────────┬─────┬───────┬─────┬──────┬───────────────────┬───────────────┐\n",
       "│ ZipCode ┆ BEV ┆ GDV   ┆ HEV ┆ PHEV ┆ TotalVehicleCount ┆ BEVProportion │\n",
       "│ ---     ┆ --- ┆ ---   ┆ --- ┆ ---  ┆ ---               ┆ ---           │\n",
       "│ i64     ┆ i64 ┆ i64   ┆ i64 ┆ i64  ┆ i64               ┆ f64           │\n",
       "╞═════════╪═════╪═══════╪═════╪══════╪═══════════════════╪═══════════════╡\n",
       "│ 35004   ┆ 55  ┆ 11075 ┆ 332 ┆ 18   ┆ 11480             ┆ 0.004791      │\n",
       "│ 35005   ┆ 14  ┆ 7359  ┆ 124 ┆ 3    ┆ 7500              ┆ 0.001867      │\n",
       "│ 35006   ┆ 4   ┆ 3292  ┆ 44  ┆ 2    ┆ 3342              ┆ 0.001197      │\n",
       "│ 35007   ┆ 134 ┆ 26722 ┆ 745 ┆ 35   ┆ 27636             ┆ 0.004849      │\n",
       "│ 35010   ┆ 44  ┆ 19750 ┆ 440 ┆ 21   ┆ 20255             ┆ 0.002172      │\n",
       "│ 35011   ┆ 0   ┆ 80    ┆ 0   ┆ 0    ┆ 80                ┆ 0.0           │\n",
       "│ 35013   ┆ 0   ┆ 3     ┆ 0   ┆ 0    ┆ 3                 ┆ 0.0           │\n",
       "│ 35014   ┆ 8   ┆ 4789  ┆ 89  ┆ 4    ┆ 4890              ┆ 0.001636      │\n",
       "│ 35016   ┆ 73  ┆ 18534 ┆ 430 ┆ 25   ┆ 19062             ┆ 0.00383       │\n",
       "│ 35019   ┆ 3   ┆ 2728  ┆ 43  ┆ 5    ┆ 2779              ┆ 0.00108       │\n",
       "└─────────┴─────┴───────┴─────┴──────┴───────────────────┴───────────────┘"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zipcodes_wide = pivot_and_enrich(zipcodes_long, \"ZipCode\")\n",
    "print(\"Output shape:\", zipcodes_wide.shape)\n",
    "zipcodes_wide.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "cell-15",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved Crosstab_ALZipCodes.csv\n"
     ]
    }
   ],
   "source": [
    "zipcodes_wide.write_csv(\"Crosstab_ALZipCodes.csv\")\n",
    "print(\"Saved Crosstab_ALZipCodes.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-16",
   "metadata": {},
   "source": "## Summary\n\nThree crosstabulation files have been written:\n\n| File | Rows | Columns |\n|---|---|---|\n| `Crosstab_USStates.csv` | 51 (50 states + DC) | State, BEV, GDV, HEV, HFCV, PHEV, TotalVehicleCount, BEVProportion |\n| `Crosstab_ALCounties.csv` | 67 counties | County, BEV, GDV, HEV, PHEV, TotalVehicleCount, BEVProportion |\n| `Crosstab_ALZipCodes.csv` | 757 ZIP codes | ZipCode, BEV, GDV, HEV, PHEV, TotalVehicleCount, BEVProportion |\n\nEach file has one row per location. The fuel-type columns contain vehicle counts,\n`TotalVehicleCount` is their sum, and `BEVProportion` is `BEV / TotalVehicleCount`."
  },
  {
   "cell_type": "markdown",
   "id": "e64d1305",
   "metadata": {},
   "source": [
    "# ZIP Code Comparisons: ACS Demographics + EV Registrations\n",
    "\n",
    "This notebook joins demographic data from the American Community Survey (ACS) with\n",
    "vehicle-registration crosstabulation data to produce a single ZIP-code-level\n",
    "comparison table.\n",
    "\n",
    "### Inputs\n",
    "\n",
    "| Source | File | Description |\n",
    "|---|---|---|\n",
    "| ACS 5-Year | `ACS_ALZipCodes.csv` | ACS data — one row per ZIP code, variables in columns |\n",
    "| Crosstabulation | `Crosstab_ALZipCodes.csv` | Pivoted vehicle counts produced above |\n",
    "\n",
    "### Variables extracted from ACS\n",
    "\n",
    "| Variable | Description |\n",
    "|---|---|\n",
    "| `B01003_E001` | Total population |\n",
    "| `LandArea` | Land area in square miles |\n",
    "| `PopulationDensity` | Land area per capita (square miles per person) |\n",
    "\n",
    "### Columns extracted from Crosstabulation\n",
    "\n",
    "| Column | Description |\n",
    "|---|---|\n",
    "| `ZipCode` | ZIP code identifier |\n",
    "| `BEV` | Battery Electric Vehicle count |\n",
    "| `TotalVehicleCount` | Total registered vehicles across all fuel types |\n",
    "| `BEVProportion` | BEV / TotalVehicleCount |\n",
    "\n",
    "### Join strategy\n",
    "\n",
    "An **outer join** on `ZipCode` is used so that no ZIP codes are lost from either\n",
    "source.  ZIP codes present in ACS but absent from vehicle registrations (or vice\n",
    "versa) will appear with `null` values in the columns from the other source.\n",
    "\n",
    "### Output\n",
    "\n",
    "| File | Description |\n",
    "|---|---|\n",
    "| `ZipCodeComparisons.csv` | Joined table with one row per ZIP code |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a284f86",
   "metadata": {},
   "source": [
    "## 1. Extract ACS demographic variables\n",
    "\n",
    "The ACS file has one row per ZIP code (`Location`) and one column per variable.\n",
    "We select the three variables of interest and rename `Location` → `ZipCode`\n",
    "and `B01003_E001` → `Population`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "dffbdb5e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ACS raw shape: (656, 910)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (3, 910)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>Location</th><th>B01002_E001</th><th>B01002_E002</th><th>B01002_E003</th><th>B01003_E001</th><th>B02001_E001</th><th>B02001_E002</th><th>B02001_E003</th><th>B02001_E004</th><th>B02001_E005</th><th>B02001_E006</th><th>B02001_E007</th><th>B02001_E008</th><th>B02001_E009</th><th>B02001_E010</th><th>B05001_E001</th><th>B05001_E002</th><th>B05001_E003</th><th>B05001_E004</th><th>B05001_E005</th><th>B05001_E006</th><th>B05002_E001</th><th>B05002_E002</th><th>B05002_E003</th><th>B05002_E004</th><th>B05002_E005</th><th>B05002_E006</th><th>B05002_E007</th><th>B05002_E008</th><th>B05002_E009</th><th>B05002_E010</th><th>B05002_E011</th><th>B05002_E012</th><th>B05002_E013</th><th>B05002_E014</th><th>B05002_E015</th><th>B05002_E016</th><th>&hellip;</th><th>C24050_E050</th><th>C24050_E051</th><th>C24050_E052</th><th>C24050_E053</th><th>C24050_E054</th><th>C24050_E055</th><th>C24050_E056</th><th>C24050_E057</th><th>C24050_E058</th><th>C24050_E059</th><th>C24050_E060</th><th>C24050_E061</th><th>C24050_E062</th><th>C24050_E063</th><th>C24050_E064</th><th>C24050_E065</th><th>C24050_E066</th><th>C24050_E067</th><th>C24050_E068</th><th>C24050_E069</th><th>C24050_E070</th><th>C24050_E071</th><th>C24050_E072</th><th>C24050_E073</th><th>C24050_E074</th><th>C24050_E075</th><th>C24050_E076</th><th>C24050_E077</th><th>C24050_E078</th><th>C24050_E079</th><th>C24050_E080</th><th>C24050_E081</th><th>C24050_E082</th><th>C24050_E083</th><th>C24050_E084</th><th>LandArea</th><th>PopulationDensity</th></tr><tr><td>i64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>&hellip;</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>35004</td><td>43.2</td><td>41.6</td><td>44.6</td><td>12155.0</td><td>12155.0</td><td>9472.0</td><td>1646.0</td><td>141.0</td><td>106.0</td><td>0.0</td><td>0.0</td><td>790.0</td><td>224.0</td><td>566.0</td><td>12155.0</td><td>11705.0</td><td>0.0</td><td>104.0</td><td>274.0</td><td>72.0</td><td>12155.0</td><td>11809.0</td><td>8042.0</td><td>3663.0</td><td>708.0</td><td>707.0</td><td>2000.0</td><td>248.0</td><td>104.0</td><td>0.0</td><td>0.0</td><td>104.0</td><td>346.0</td><td>274.0</td><td>33.0</td><td>124.0</td><td>&hellip;</td><td>16.0</td><td>341.0</td><td>123.0</td><td>341.0</td><td>65.0</td><td>102.0</td><td>0.0</td><td>476.0</td><td>0.0</td><td>283.0</td><td>0.0</td><td>0.0</td><td>78.0</td><td>23.0</td><td>0.0</td><td>10.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>41.0</td><td>41.0</td><td>797.0</td><td>0.0</td><td>0.0</td><td>219.0</td><td>64.0</td><td>201.0</td><td>155.0</td><td>38.0</td><td>0.0</td><td>102.0</td><td>18.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>17.239</td><td>0.001418</td></tr><tr><td>35005</td><td>41.7</td><td>36.9</td><td>46.2</td><td>8247.0</td><td>8247.0</td><td>3022.0</td><td>4559.0</td><td>0.0</td><td>86.0</td><td>538.0</td><td>0.0</td><td>42.0</td><td>18.0</td><td>24.0</td><td>8247.0</td><td>7955.0</td><td>0.0</td><td>0.0</td><td>30.0</td><td>262.0</td><td>8247.0</td><td>7955.0</td><td>6295.0</td><td>1660.0</td><td>63.0</td><td>659.0</td><td>795.0</td><td>143.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>292.0</td><td>30.0</td><td>2.0</td><td>28.0</td><td>&hellip;</td><td>53.0</td><td>382.0</td><td>32.0</td><td>203.0</td><td>2.0</td><td>4.0</td><td>34.0</td><td>240.0</td><td>0.0</td><td>81.0</td><td>2.0</td><td>5.0</td><td>3.0</td><td>39.0</td><td>0.0</td><td>41.0</td><td>19.0</td><td>5.0</td><td>21.0</td><td>24.0</td><td>0.0</td><td>840.0</td><td>0.0</td><td>18.0</td><td>196.0</td><td>38.0</td><td>274.0</td><td>158.0</td><td>0.0</td><td>0.0</td><td>33.0</td><td>39.0</td><td>3.0</td><td>65.0</td><td>16.0</td><td>31.747</td><td>0.00385</td></tr><tr><td>35006</td><td>38.5</td><td>38.6</td><td>38.5</td><td>3894.0</td><td>3894.0</td><td>3468.0</td><td>313.0</td><td>2.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>111.0</td><td>94.0</td><td>17.0</td><td>3894.0</td><td>3884.0</td><td>0.0</td><td>0.0</td><td>10.0</td><td>0.0</td><td>3894.0</td><td>3884.0</td><td>3418.0</td><td>466.0</td><td>56.0</td><td>97.0</td><td>293.0</td><td>20.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>10.0</td><td>10.0</td><td>0.0</td><td>0.0</td><td>&hellip;</td><td>0.0</td><td>39.0</td><td>80.0</td><td>98.0</td><td>0.0</td><td>12.0</td><td>190.0</td><td>354.0</td><td>46.0</td><td>87.0</td><td>40.0</td><td>0.0</td><td>95.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>55.0</td><td>0.0</td><td>0.0</td><td>14.0</td><td>17.0</td><td>301.0</td><td>0.0</td><td>24.0</td><td>144.0</td><td>0.0</td><td>28.0</td><td>19.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>42.0</td><td>0.0</td><td>44.0</td><td>0.0</td><td>103.993</td><td>0.026706</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (3, 910)\n",
       "┌──────────┬─────────────┬─────────────┬─────────────┬───┬─────────────┬─────────────┬──────────┬───────────────────┐\n",
       "│ Location ┆ B01002_E001 ┆ B01002_E002 ┆ B01002_E003 ┆ … ┆ C24050_E083 ┆ C24050_E084 ┆ LandArea ┆ PopulationDensity │\n",
       "│ ---      ┆ ---         ┆ ---         ┆ ---         ┆   ┆ ---         ┆ ---         ┆ ---      ┆ ---               │\n",
       "│ i64      ┆ f64         ┆ f64         ┆ f64         ┆   ┆ f64         ┆ f64         ┆ f64      ┆ f64               │\n",
       "╞══════════╪═════════════╪═════════════╪═════════════╪═══╪═════════════╪═════════════╪══════════╪═══════════════════╡\n",
       "│ 35004    ┆ 43.2        ┆ 41.6        ┆ 44.6        ┆ … ┆ 0.0         ┆ 0.0         ┆ 17.239   ┆ 0.001418          │\n",
       "│ 35005    ┆ 41.7        ┆ 36.9        ┆ 46.2        ┆ … ┆ 65.0        ┆ 16.0        ┆ 31.747   ┆ 0.00385           │\n",
       "│ 35006    ┆ 38.5        ┆ 38.6        ┆ 38.5        ┆ … ┆ 44.0        ┆ 0.0         ┆ 103.993  ┆ 0.026706          │\n",
       "└──────────┴─────────────┴─────────────┴─────────────┴───┴─────────────┴─────────────┴──────────┴───────────────────┘"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "acs_raw = pl.read_csv(\"ACS_ALZipCodes.csv\")\n",
    "print(\"ACS raw shape:\", acs_raw.shape)\n",
    "acs_raw.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3ce776fb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Selected 3 variables for 656 ZIP codes\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>Location</th><th>B01003_E001</th><th>LandArea</th><th>PopulationDensity</th></tr><tr><td>i64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>35004</td><td>12155.0</td><td>17.239</td><td>0.001418</td></tr><tr><td>35005</td><td>8247.0</td><td>31.747</td><td>0.00385</td></tr><tr><td>35006</td><td>3894.0</td><td>103.993</td><td>0.026706</td></tr><tr><td>35007</td><td>28586.0</td><td>39.048</td><td>0.001366</td></tr><tr><td>35010</td><td>19347.0</td><td>222.707</td><td>0.011511</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 4)\n",
       "┌──────────┬─────────────┬──────────┬───────────────────┐\n",
       "│ Location ┆ B01003_E001 ┆ LandArea ┆ PopulationDensity │\n",
       "│ ---      ┆ ---         ┆ ---      ┆ ---               │\n",
       "│ i64      ┆ f64         ┆ f64      ┆ f64               │\n",
       "╞══════════╪═════════════╪══════════╪═══════════════════╡\n",
       "│ 35004    ┆ 12155.0     ┆ 17.239   ┆ 0.001418          │\n",
       "│ 35005    ┆ 8247.0      ┆ 31.747   ┆ 0.00385           │\n",
       "│ 35006    ┆ 3894.0      ┆ 103.993  ┆ 0.026706          │\n",
       "│ 35007    ┆ 28586.0     ┆ 39.048   ┆ 0.001366          │\n",
       "│ 35010    ┆ 19347.0     ┆ 222.707  ┆ 0.011511          │\n",
       "└──────────┴─────────────┴──────────┴───────────────────┘"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select the three variables of interest plus the location key\n",
    "acs_vars = [\"B01003_E001\", \"LandArea\", \"PopulationDensity\"]\n",
    "acs_selected = acs_raw.select(\"Location\", *acs_vars)\n",
    "print(\"Selected\", len(acs_vars), \"variables for\", acs_selected.height, \"ZIP codes\")\n",
    "acs_selected.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca611b31",
   "metadata": {},
   "source": [
    "### Rename and cast columns\n",
    "\n",
    "Rename `Location` → `ZipCode` and `B01003_E001` → `Population`, then cast\n",
    "all columns to the expected types so they match the crosstabulation data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9565e55b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ACS shape: (656, 4)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (10, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ZipCode</th><th>Population</th><th>LandArea</th><th>PopulationDensity</th></tr><tr><td>i64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>35004</td><td>12155.0</td><td>17.239</td><td>0.001418</td></tr><tr><td>35005</td><td>8247.0</td><td>31.747</td><td>0.00385</td></tr><tr><td>35006</td><td>3894.0</td><td>103.993</td><td>0.026706</td></tr><tr><td>35007</td><td>28586.0</td><td>39.048</td><td>0.001366</td></tr><tr><td>35010</td><td>19347.0</td><td>222.707</td><td>0.011511</td></tr><tr><td>35013</td><td>141.0</td><td>0.028</td><td>0.000199</td></tr><tr><td>35014</td><td>3219.0</td><td>78.854</td><td>0.024496</td></tr><tr><td>35016</td><td>17158.0</td><td>78.972</td><td>0.004603</td></tr><tr><td>35019</td><td>2197.0</td><td>28.259</td><td>0.012863</td></tr><tr><td>35020</td><td>24892.0</td><td>16.555</td><td>0.000665</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (10, 4)\n",
       "┌─────────┬────────────┬──────────┬───────────────────┐\n",
       "│ ZipCode ┆ Population ┆ LandArea ┆ PopulationDensity │\n",
       "│ ---     ┆ ---        ┆ ---      ┆ ---               │\n",
       "│ i64     ┆ f64        ┆ f64      ┆ f64               │\n",
       "╞═════════╪════════════╪══════════╪═══════════════════╡\n",
       "│ 35004   ┆ 12155.0    ┆ 17.239   ┆ 0.001418          │\n",
       "│ 35005   ┆ 8247.0     ┆ 31.747   ┆ 0.00385           │\n",
       "│ 35006   ┆ 3894.0     ┆ 103.993  ┆ 0.026706          │\n",
       "│ 35007   ┆ 28586.0    ┆ 39.048   ┆ 0.001366          │\n",
       "│ 35010   ┆ 19347.0    ┆ 222.707  ┆ 0.011511          │\n",
       "│ 35013   ┆ 141.0      ┆ 0.028    ┆ 0.000199          │\n",
       "│ 35014   ┆ 3219.0     ┆ 78.854   ┆ 0.024496          │\n",
       "│ 35016   ┆ 17158.0    ┆ 78.972   ┆ 0.004603          │\n",
       "│ 35019   ┆ 2197.0     ┆ 28.259   ┆ 0.012863          │\n",
       "│ 35020   ┆ 24892.0    ┆ 16.555   ┆ 0.000665          │\n",
       "└─────────┴────────────┴──────────┴───────────────────┘"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "acs_transposed = (\n",
    "    acs_selected\n",
    "    .rename({\"Location\": \"ZipCode\", \"B01003_E001\": \"Population\"})\n",
    "    .with_columns(\n",
    "        pl.col(\"ZipCode\").cast(pl.Int64),\n",
    "        pl.col(\"Population\").cast(pl.Float64),\n",
    "        pl.col(\"LandArea\").cast(pl.Float64),\n",
    "        pl.col(\"PopulationDensity\").cast(pl.Float64),\n",
    "    )\n",
    "    .sort(\"ZipCode\")\n",
    ")\n",
    "\n",
    "print(\"ACS shape:\", acs_transposed.shape)\n",
    "acs_transposed.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f525247c",
   "metadata": {},
   "source": "## 2. Load the crosstabulation extract\n\nFrom `Crosstab_ALZipCodes.csv` (produced in the section above) we keep\nonly the four columns needed for the comparison: `ZipCode`, `BEV`,\n`TotalVehicleCount`, and `BEVProportion`."
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7b3e54ce",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Crosstab raw shape: (757, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ZipCode</th><th>BEV</th><th>GDV</th><th>HEV</th><th>PHEV</th><th>TotalVehicleCount</th><th>BEVProportion</th></tr><tr><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>35004</td><td>55</td><td>11075</td><td>332</td><td>18</td><td>11480</td><td>0.004791</td></tr><tr><td>35005</td><td>14</td><td>7359</td><td>124</td><td>3</td><td>7500</td><td>0.001867</td></tr><tr><td>35006</td><td>4</td><td>3292</td><td>44</td><td>2</td><td>3342</td><td>0.001197</td></tr><tr><td>35007</td><td>134</td><td>26722</td><td>745</td><td>35</td><td>27636</td><td>0.004849</td></tr><tr><td>35010</td><td>44</td><td>19750</td><td>440</td><td>21</td><td>20255</td><td>0.002172</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌─────────┬─────┬───────┬─────┬──────┬───────────────────┬───────────────┐\n",
       "│ ZipCode ┆ BEV ┆ GDV   ┆ HEV ┆ PHEV ┆ TotalVehicleCount ┆ BEVProportion │\n",
       "│ ---     ┆ --- ┆ ---   ┆ --- ┆ ---  ┆ ---               ┆ ---           │\n",
       "│ i64     ┆ i64 ┆ i64   ┆ i64 ┆ i64  ┆ i64               ┆ f64           │\n",
       "╞═════════╪═════╪═══════╪═════╪══════╪═══════════════════╪═══════════════╡\n",
       "│ 35004   ┆ 55  ┆ 11075 ┆ 332 ┆ 18   ┆ 11480             ┆ 0.004791      │\n",
       "│ 35005   ┆ 14  ┆ 7359  ┆ 124 ┆ 3    ┆ 7500              ┆ 0.001867      │\n",
       "│ 35006   ┆ 4   ┆ 3292  ┆ 44  ┆ 2    ┆ 3342              ┆ 0.001197      │\n",
       "│ 35007   ┆ 134 ┆ 26722 ┆ 745 ┆ 35   ┆ 27636             ┆ 0.004849      │\n",
       "│ 35010   ┆ 44  ┆ 19750 ┆ 440 ┆ 21   ┆ 20255             ┆ 0.002172      │\n",
       "└─────────┴─────┴───────┴─────┴──────┴───────────────────┴───────────────┘"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crosstab = pl.read_csv(\"Crosstab_ALZipCodes.csv\")\n",
    "print(\"Crosstab raw shape:\", crosstab.shape)\n",
    "crosstab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "756b4b0e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Crosstab extract shape: (757, 4)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (10, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ZipCode</th><th>BEV</th><th>TotalVehicleCount</th><th>BEVProportion</th></tr><tr><td>i64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>35004</td><td>55</td><td>11480</td><td>0.004791</td></tr><tr><td>35005</td><td>14</td><td>7500</td><td>0.001867</td></tr><tr><td>35006</td><td>4</td><td>3342</td><td>0.001197</td></tr><tr><td>35007</td><td>134</td><td>27636</td><td>0.004849</td></tr><tr><td>35010</td><td>44</td><td>20255</td><td>0.002172</td></tr><tr><td>35011</td><td>0</td><td>80</td><td>0.0</td></tr><tr><td>35013</td><td>0</td><td>3</td><td>0.0</td></tr><tr><td>35014</td><td>8</td><td>4890</td><td>0.001636</td></tr><tr><td>35016</td><td>73</td><td>19062</td><td>0.00383</td></tr><tr><td>35019</td><td>3</td><td>2779</td><td>0.00108</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (10, 4)\n",
       "┌─────────┬─────┬───────────────────┬───────────────┐\n",
       "│ ZipCode ┆ BEV ┆ TotalVehicleCount ┆ BEVProportion │\n",
       "│ ---     ┆ --- ┆ ---               ┆ ---           │\n",
       "│ i64     ┆ i64 ┆ i64               ┆ f64           │\n",
       "╞═════════╪═════╪═══════════════════╪═══════════════╡\n",
       "│ 35004   ┆ 55  ┆ 11480             ┆ 0.004791      │\n",
       "│ 35005   ┆ 14  ┆ 7500              ┆ 0.001867      │\n",
       "│ 35006   ┆ 4   ┆ 3342              ┆ 0.001197      │\n",
       "│ 35007   ┆ 134 ┆ 27636             ┆ 0.004849      │\n",
       "│ 35010   ┆ 44  ┆ 20255             ┆ 0.002172      │\n",
       "│ 35011   ┆ 0   ┆ 80                ┆ 0.0           │\n",
       "│ 35013   ┆ 0   ┆ 3                 ┆ 0.0           │\n",
       "│ 35014   ┆ 8   ┆ 4890              ┆ 0.001636      │\n",
       "│ 35016   ┆ 73  ┆ 19062             ┆ 0.00383       │\n",
       "│ 35019   ┆ 3   ┆ 2779              ┆ 0.00108       │\n",
       "└─────────┴─────┴───────────────────┴───────────────┘"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crosstab_extract = crosstab.select(\"ZipCode\", \"BEV\", \"TotalVehicleCount\", \"BEVProportion\")\n",
    "print(\"Crosstab extract shape:\", crosstab_extract.shape)\n",
    "crosstab_extract.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07defbbb",
   "metadata": {},
   "source": [
    "## 3. Outer join on ZipCode\n",
    "\n",
    "An outer join preserves every ZIP code from **both** sources:\n",
    "\n",
    "- ZIP codes in ACS but not in vehicle registrations will have `null` for BEV,\n",
    "  TotalVehicleCount, and BEVProportion.\n",
    "- ZIP codes in vehicle registrations but not in ACS will have `null` for\n",
    "  Population, LandArea, and PopulationDensity.\n",
    "\n",
    "The `coalesce=True` parameter merges the two `ZipCode` key columns into one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "3e05a198",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joined shape: (760, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (10, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ZipCode</th><th>Population</th><th>LandArea</th><th>PopulationDensity</th><th>BEV</th><th>TotalVehicleCount</th><th>BEVProportion</th></tr><tr><td>i64</td><td>f64</td><td>f64</td><td>f64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>35004</td><td>12155.0</td><td>17.239</td><td>0.001418</td><td>55</td><td>11480</td><td>0.004791</td></tr><tr><td>35005</td><td>8247.0</td><td>31.747</td><td>0.00385</td><td>14</td><td>7500</td><td>0.001867</td></tr><tr><td>35006</td><td>3894.0</td><td>103.993</td><td>0.026706</td><td>4</td><td>3342</td><td>0.001197</td></tr><tr><td>35007</td><td>28586.0</td><td>39.048</td><td>0.001366</td><td>134</td><td>27636</td><td>0.004849</td></tr><tr><td>35010</td><td>19347.0</td><td>222.707</td><td>0.011511</td><td>44</td><td>20255</td><td>0.002172</td></tr><tr><td>35011</td><td>null</td><td>null</td><td>null</td><td>0</td><td>80</td><td>0.0</td></tr><tr><td>35013</td><td>141.0</td><td>0.028</td><td>0.000199</td><td>0</td><td>3</td><td>0.0</td></tr><tr><td>35014</td><td>3219.0</td><td>78.854</td><td>0.024496</td><td>8</td><td>4890</td><td>0.001636</td></tr><tr><td>35016</td><td>17158.0</td><td>78.972</td><td>0.004603</td><td>73</td><td>19062</td><td>0.00383</td></tr><tr><td>35019</td><td>2197.0</td><td>28.259</td><td>0.012863</td><td>3</td><td>2779</td><td>0.00108</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (10, 7)\n",
       "┌─────────┬────────────┬──────────┬───────────────────┬─────┬───────────────────┬───────────────┐\n",
       "│ ZipCode ┆ Population ┆ LandArea ┆ PopulationDensity ┆ BEV ┆ TotalVehicleCount ┆ BEVProportion │\n",
       "│ ---     ┆ ---        ┆ ---      ┆ ---               ┆ --- ┆ ---               ┆ ---           │\n",
       "│ i64     ┆ f64        ┆ f64      ┆ f64               ┆ i64 ┆ i64               ┆ f64           │\n",
       "╞═════════╪════════════╪══════════╪═══════════════════╪═════╪═══════════════════╪═══════════════╡\n",
       "│ 35004   ┆ 12155.0    ┆ 17.239   ┆ 0.001418          ┆ 55  ┆ 11480             ┆ 0.004791      │\n",
       "│ 35005   ┆ 8247.0     ┆ 31.747   ┆ 0.00385           ┆ 14  ┆ 7500              ┆ 0.001867      │\n",
       "│ 35006   ┆ 3894.0     ┆ 103.993  ┆ 0.026706          ┆ 4   ┆ 3342              ┆ 0.001197      │\n",
       "│ 35007   ┆ 28586.0    ┆ 39.048   ┆ 0.001366          ┆ 134 ┆ 27636             ┆ 0.004849      │\n",
       "│ 35010   ┆ 19347.0    ┆ 222.707  ┆ 0.011511          ┆ 44  ┆ 20255             ┆ 0.002172      │\n",
       "│ 35011   ┆ null       ┆ null     ┆ null              ┆ 0   ┆ 80                ┆ 0.0           │\n",
       "│ 35013   ┆ 141.0      ┆ 0.028    ┆ 0.000199          ┆ 0   ┆ 3                 ┆ 0.0           │\n",
       "│ 35014   ┆ 3219.0     ┆ 78.854   ┆ 0.024496          ┆ 8   ┆ 4890              ┆ 0.001636      │\n",
       "│ 35016   ┆ 17158.0    ┆ 78.972   ┆ 0.004603          ┆ 73  ┆ 19062             ┆ 0.00383       │\n",
       "│ 35019   ┆ 2197.0     ┆ 28.259   ┆ 0.012863          ┆ 3   ┆ 2779              ┆ 0.00108       │\n",
       "└─────────┴────────────┴──────────┴───────────────────┴─────┴───────────────────┴───────────────┘"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined = (\n",
    "    acs_transposed\n",
    "    .join(crosstab_extract, on=\"ZipCode\", how=\"full\", coalesce=True)\n",
    "    .sort(\"ZipCode\")\n",
    ")\n",
    "\n",
    "print(\"Joined shape:\", joined.shape)\n",
    "joined.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b99d8b9d",
   "metadata": {},
   "source": [
    "### Check for mismatches\n",
    "\n",
    "Inspect rows where one side of the join has `null` values, to understand which\n",
    "ZIP codes exist in only one of the two sources."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f7f8c7c1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ZIP codes in both sources:    653\n",
      "ZIP codes in ACS only:        3\n",
      "ZIP codes in EV data only:    104\n",
      "Total rows:                   760\n"
     ]
    }
   ],
   "source": [
    "acs_only = joined.filter(pl.col(\"TotalVehicleCount\").is_null())\n",
    "ev_only = joined.filter(pl.col(\"Population\").is_null())\n",
    "both = joined.filter(pl.col(\"Population\").is_not_null() & pl.col(\"TotalVehicleCount\").is_not_null())\n",
    "\n",
    "print(f\"ZIP codes in both sources:    {both.height}\")\n",
    "print(f\"ZIP codes in ACS only:        {acs_only.height}\")\n",
    "print(f\"ZIP codes in EV data only:    {ev_only.height}\")\n",
    "print(f\"Total rows:                   {joined.height}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sd5etr3z3ll",
   "metadata": {},
   "source": [
    "### Coverage loss by minimum-size threshold\n",
    "\n",
    "How much of each measure do we lose if we exclude ZIP codes that are too small\n",
    "or missing from one source?  A ZIP code is **excluded** when either its\n",
    "`Population` or `TotalVehicleCount` is `null` *or* falls below a given threshold.\n",
    "\n",
    "Each row in the table below represents a threshold value.  The columns show the\n",
    "**proportion lost** (0 = nothing lost, 1 = everything lost) for each measure.\n",
    "Threshold 0 corresponds to dropping only the truly missing ZIP codes (nulls);\n",
    "higher thresholds also drop small ZIP codes from both sides."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "k3h5ijh29bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (12, 6)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>Threshold</th><th>ZipCodes Kept</th><th>Population</th><th>LandArea</th><th>BEV</th><th>TotalVehicleCount</th></tr><tr><td>i64</td><td>i64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>0</td><td>653</td><td>0.000237</td><td>0.000002</td><td>0.00004</td><td>0.000425</td></tr><tr><td>100</td><td>587</td><td>0.003505</td><td>0.005436</td><td>0.000404</td><td>0.001123</td></tr><tr><td>200</td><td>577</td><td>0.004906</td><td>0.0091</td><td>0.000484</td><td>0.001535</td></tr><tr><td>300</td><td>568</td><td>0.005492</td><td>0.015371</td><td>0.000888</td><td>0.002182</td></tr><tr><td>400</td><td>560</td><td>0.006282</td><td>0.022185</td><td>0.001412</td><td>0.002945</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>650</td><td>537</td><td>0.00893</td><td>0.051579</td><td>0.002139</td><td>0.006644</td></tr><tr><td>675</td><td>531</td><td>0.009718</td><td>0.063829</td><td>0.002421</td><td>0.007793</td></tr><tr><td>680</td><td>530</td><td>0.009851</td><td>0.065228</td><td>0.002421</td><td>0.007963</td></tr><tr><td>690</td><td>529</td><td>0.009985</td><td>0.067944</td><td>0.002462</td><td>0.008183</td></tr><tr><td>700</td><td>527</td><td>0.01036</td><td>0.069498</td><td>0.002462</td><td>0.008503</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (12, 6)\n",
       "┌───────────┬───────────────┬────────────┬──────────┬──────────┬───────────────────┐\n",
       "│ Threshold ┆ ZipCodes Kept ┆ Population ┆ LandArea ┆ BEV      ┆ TotalVehicleCount │\n",
       "│ ---       ┆ ---           ┆ ---        ┆ ---      ┆ ---      ┆ ---               │\n",
       "│ i64       ┆ i64           ┆ f64        ┆ f64      ┆ f64      ┆ f64               │\n",
       "╞═══════════╪═══════════════╪════════════╪══════════╪══════════╪═══════════════════╡\n",
       "│ 0         ┆ 653           ┆ 0.000237   ┆ 0.000002 ┆ 0.00004  ┆ 0.000425          │\n",
       "│ 100       ┆ 587           ┆ 0.003505   ┆ 0.005436 ┆ 0.000404 ┆ 0.001123          │\n",
       "│ 200       ┆ 577           ┆ 0.004906   ┆ 0.0091   ┆ 0.000484 ┆ 0.001535          │\n",
       "│ 300       ┆ 568           ┆ 0.005492   ┆ 0.015371 ┆ 0.000888 ┆ 0.002182          │\n",
       "│ 400       ┆ 560           ┆ 0.006282   ┆ 0.022185 ┆ 0.001412 ┆ 0.002945          │\n",
       "│ …         ┆ …             ┆ …          ┆ …        ┆ …        ┆ …                 │\n",
       "│ 650       ┆ 537           ┆ 0.00893    ┆ 0.051579 ┆ 0.002139 ┆ 0.006644          │\n",
       "│ 675       ┆ 531           ┆ 0.009718   ┆ 0.063829 ┆ 0.002421 ┆ 0.007793          │\n",
       "│ 680       ┆ 530           ┆ 0.009851   ┆ 0.065228 ┆ 0.002421 ┆ 0.007963          │\n",
       "│ 690       ┆ 529           ┆ 0.009985   ┆ 0.067944 ┆ 0.002462 ┆ 0.008183          │\n",
       "│ 700       ┆ 527           ┆ 0.01036    ┆ 0.069498 ┆ 0.002462 ┆ 0.008503          │\n",
       "└───────────┴───────────────┴────────────┴──────────┴──────────┴───────────────────┘"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "thresholds = [0, 100, 200, 300, 400, 500, 600, 650, 675, 680, 690, 700]\n",
    "measures = [\"Population\", \"LandArea\", \"BEV\", \"TotalVehicleCount\"]\n",
    "\n",
    "# Universe totals: sum of each measure across all non-null ZIP codes\n",
    "totals = {m: joined.select(pl.col(m).sum()).item() for m in measures}\n",
    "\n",
    "rows = []\n",
    "for t in thresholds:\n",
    "    # Keep ZIP codes where both Population and TotalVehicleCount are non-null\n",
    "    # and at least as large as the threshold\n",
    "    kept = joined.filter(\n",
    "        (pl.col(\"Population\").is_not_null() & (pl.col(\"Population\") >= t))\n",
    "        & (pl.col(\"TotalVehicleCount\").is_not_null() & (pl.col(\"TotalVehicleCount\") >= t))\n",
    "    )\n",
    "    row = {\"Threshold\": t, \"ZipCodes Kept\": kept.height}\n",
    "    for m in measures:\n",
    "        retained = kept.select(pl.col(m).sum()).item()\n",
    "        row[m] = round((totals[m] - retained) / totals[m], 6) if totals[m] else 0.0\n",
    "    rows.append(row)\n",
    "\n",
    "loss_table = pl.DataFrame(rows)\n",
    "loss_table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1acd599b",
   "metadata": {},
   "source": [
    "## 4. Save the result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "18f4e7ce",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved ZipCodeComparisons.csv\n",
      "Final columns: ['ZipCode', 'Population', 'LandArea', 'PopulationDensity', 'BEV', 'TotalVehicleCount', 'BEVProportion']\n"
     ]
    }
   ],
   "source": [
    "joined.write_csv(\"ZipCodeComparisons.csv\")\n",
    "print(\"Saved ZipCodeComparisons.csv\")\n",
    "print(\"Final columns:\", joined.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1380488c",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "The output file `ZipCodeComparisons.csv` contains one row per\n",
    "Alabama ZIP code with the following columns:\n",
    "\n",
    "| Column | Source | Description |\n",
    "|---|---|---|\n",
    "| `ZipCode` | Both | ZIP code identifier |\n",
    "| `Population` | ACS (`B01003_E001`) | Total population |\n",
    "| `LandArea` | ACS | Land area in square miles |\n",
    "| `PopulationDensity` | ACS | Land area per capita (sq mi / person) |\n",
    "| `BEV` | Crosstab | Battery Electric Vehicle count |\n",
    "| `TotalVehicleCount` | Crosstab | Total registered vehicles |\n",
    "| `BEVProportion` | Crosstab | BEV / TotalVehicleCount |"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}